package com.fau.cm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.BeanUtils;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.cm.entity.MedicalAdmin;
import com.fau.cm.entity.MedicalCallCustomer;
import com.fau.cm.entity.MedicalCity;
import com.fau.cm.entity.MedicalDistrict;
import com.fau.cm.entity.MedicalInStock;
import com.fau.cm.entity.MedicalKeyValue;
import com.fau.cm.entity.MedicalLogistics;
import com.fau.cm.entity.MedicalMediaInfo;
import com.fau.cm.entity.MedicalMedicine;
import com.fau.cm.entity.MedicalOrders;
import com.fau.cm.entity.MedicalOrdersProduct;
import com.fau.cm.entity.MedicalOrdersStorage;
import com.fau.cm.entity.MedicalProvince;
import com.fau.cm.service.IOrdersManagerService;
import com.fau.cm.util.Page;
import com.fau.cm.util.ParamUtil;
import com.fau.cm.util.SerialNumUtil;
import com.fau.cm.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class OrdersManagerServiceImpl extends BaseService implements
		IOrdersManagerService {

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String saveOrders(MedicalOrders orders, MedicalAdmin admin) {
		String uid = UUID.randomUUID().toString();
		String id = uid.replace("-", "");
		orders.setId(id);
		orders.setSerialNum(SerialNumUtil.code());
		orders.setCusSerId(admin.getId());
		orders.setHospitalId(admin.getEnterpriseId());
		MedicalProvince province = (MedicalProvince) this.iBaseDao.getObject(MedicalProvince.class, orders.getProvinceId());
		MedicalCity city = (MedicalCity) this.iBaseDao.getObject(MedicalCity.class, orders.getCityId());
		MedicalDistrict district = (MedicalDistrict) this.iBaseDao.getObject(MedicalDistrict.class, orders.getDistrictId());
		orders.setProvince(province);
		orders.setCity(city);
		orders.setDistrict(district);
		orders.setStatus(SystemConfig.ORDERS_IMPORT_TYPE);
		orders.setAllocateGrade(SystemConfig.ORDERS_GRADE_INIT);
		this.iBaseDao.addObject(orders);
		MedicalOrdersStorage ordersStorage = new MedicalOrdersStorage();
		BeanUtils.copyProperties(orders, ordersStorage);
		if(!SystemConfig.SUPER_ADMIN.equals(admin.getIsSuper())) {
			ordersStorage.setCusSerCenter(admin.getGroup().getPlatform().getId());
			ordersStorage.setCusSerGroup(admin.getGroup().getId());
		}
		ordersStorage.setCallStatus(SystemConfig.ORDERS_IMPORT_TYPE);
		this.iBaseDao.addObject(ordersStorage);
		MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
		customer.setRedEnvelopes(orders.getRedEnvelopes());
		return orders.getId();
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delOrders(MedicalOrders orders) {
		this.iBaseDao.updateHql("delete from MedicalOrdersProduct a where a.ordersId=? ", orders.getId());
		this.iBaseDao.updateHql("delete from MedicalOrdersStorage a where a.id=? ", orders.getId());
		
		MedicalOrders bean = (MedicalOrders) this.iBaseDao.getObject(MedicalOrders.class, orders.getId());
//		MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, bean.getCustomerId());
//		customer.setMoney(customer.getMoney().add(bean.getUseMoney()));	// 加回使用余额
//		customer.setIntegral(customer.getIntegral() + bean.getUsePoints());		// 加回使用积分
//		customer.setRedEnvelopes(customer.getRedEnvelopes() + bean.getUseRed());// 加回使用红包
//		customer.setRedEnvelopes(customer.getRedEnvelopes() - bean.getRed());	// 减去本单红包
		
		this.iBaseDao.updateHql("delete from MedicalOrdersLog a where a.orderNo=? ", bean.getSerialNum());
		this.iBaseDao.deleteObject(bean);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void editOrders(MedicalOrders orders, MedicalAdmin admin) {
		MedicalOrders bean = (MedicalOrders) this.iBaseDao.getObject(MedicalOrders.class, orders.getId());
		bean.setName(orders.getName());
		bean.setContactNo(orders.getContactNo());
		MedicalProvince province = (MedicalProvince) this.iBaseDao.getObject(MedicalProvince.class, orders.getProvinceId());
		MedicalCity city = (MedicalCity) this.iBaseDao.getObject(MedicalCity.class, orders.getCityId());
		MedicalDistrict district = (MedicalDistrict) this.iBaseDao.getObject(MedicalDistrict.class, orders.getDistrictId());
		bean.setProvince(province);
		bean.setCity(city);
		bean.setDistrict(district);
		bean.setAddress(orders.getAddress());
		bean.setOrdersRemark(orders.getOrdersRemark());
		bean.setInvoice(orders.getInvoice());
		bean.setMediaId(orders.getMediaId());
		bean.setNature(orders.getNature());
		bean.setExpressId(orders.getExpressId());
		bean.setPayType(orders.getPayType());
		bean.setPayment(orders.getPayment());
		bean.setFreight(orders.getFreight());
		bean.setReceivable(orders.getReceivable());
		bean.setAmountPaid(orders.getAmountPaid());
		bean.setUsePoints(orders.getUsePoints());
		bean.setPoints(orders.getPoints());
		bean.setUseRed(orders.getUseRed());
		bean.setRed(orders.getRed());
		bean.setUseMoney(orders.getUseMoney());
		bean.setUpdateTime(new Date());
		this.iBaseDao.updateHql("delete from MedicalOrdersStorage a where a.id=? ", bean.getId());
		MedicalOrdersStorage ordersStorage = new MedicalOrdersStorage();
		BeanUtils.copyProperties(bean, ordersStorage);
		if(!SystemConfig.SUPER_ADMIN.equals(admin.getIsSuper())) {
			ordersStorage.setCusSerCenter(admin.getGroup().getPlatform().getId());
			ordersStorage.setCusSerGroup(admin.getGroup().getId());
		}
		ordersStorage.setCallStatus(bean.getStatus());
		this.iBaseDao.addObject(ordersStorage);
//		MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
//		customer.setMoney(orders.getMoney());
//		customer.setIntegral(orders.getIntegral());
//		customer.setRedEnvelopes(orders.getRedEnvelopes());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalOrdersStorage> findOrdersByCid(MedicalOrders orders) {
		List<MedicalOrdersStorage> ordersStorageList = this.iBaseDao.getBySql("from MedicalOrdersStorage a where a.customerId=? order by a.createTime desc ", null, orders.getCustomerId());
		List<MedicalOrdersStorage> list = new ArrayList<MedicalOrdersStorage>();
		for (MedicalOrdersStorage bean : ordersStorageList) {
			StringBuilder ordersProduct = new StringBuilder("");
			MedicalKeyValue payType = (MedicalKeyValue)this.iBaseDao.getOmValueByKey(bean.getHospitalId(), bean.getPayType());
			bean.setPayTypeVal(payType.getKeyValue());
			List<MedicalOrdersProduct> opList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, bean.getId());
			for(MedicalOrdersProduct op : opList) {
				ordersProduct.append(op.getMedicine().getName());
				ordersProduct.append("；");
			}
			if(StringUtils.isNotBlank(ordersProduct.toString())) {
				ordersProduct.deleteCharAt(ordersProduct.lastIndexOf("；"));
			}
			bean.setOrdersProduct(ordersProduct.toString());
			list.add(bean);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalMediaInfo> findMediaList(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalMediaInfo a where a.eid=? ", null, admin.getEnterpriseId());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalLogistics> findLogisticsList(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalLogistics a where a.hospitalId=? ", null, admin.getEnterpriseId());
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page findMedAndPreByPage(MedicalOrdersProduct ordersProduct, Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalMedicine a where 1=1 and a.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalMedicine a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(ordersProduct.getHospitalId());
		
		// 药品名称
		if(StringUtils.isNotBlank(ordersProduct.getMedName())){
			countSql.append("and a.name like ? ");
			statusSql.append("and a.name like ? ");
			objectList.add("%"+ordersProduct.getMedName()+"%");
		}
		// 产品类型
		if(StringUtils.isNotBlank(ordersProduct.getMedType())){
			countSql.append("and a.medicineType=? ");
			statusSql.append("and a.medicineType=? ");
			objectList.add(ordersProduct.getMedType());
		}
		statusSql.append("order by a.createTime desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalMedicine> list = new ArrayList<MedicalMedicine>();
		for(Object obj : page.getResult()) {
			MedicalMedicine bean = (MedicalMedicine) obj;
			MedicalKeyValue medicineType = (MedicalKeyValue) this.iBaseDao.getObjectByHql("from MedicalKeyValue a where a.hospitalId=? and a.systemType=? and a.keyName=? ", bean.getHospitalId(), SystemConfig.WM_SYSTEM_TYPE, bean.getMedicineType());
			if(null != medicineType) {
				bean.setMedicineTypeName(medicineType.getKeyValue());
			}
			
			Integer inventory = 0;
			List<MedicalInStock> inStocks = this.iBaseDao.getBySql("from MedicalInStock a where a.medicineId=? ", null, bean.getId());
			for(MedicalInStock inStock : inStocks) {
				inventory += inStock.getQuantity();
			}
			bean.setInventory(inventory);
			list.add(bean);
		}
		page.setResult(list);
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveOrdersProduct(MedicalOrdersProduct ordersProduct) {
		BigDecimal quantity = new BigDecimal(ordersProduct.getQuantity());		// 数量
		BigDecimal totalPrice = ordersProduct.getPrePrice().multiply(quantity);	// 实际单价 * 数量
		BigDecimal usePoints = new BigDecimal(ordersProduct.getUsePoints());	// 积分
		BigDecimal useRed = new BigDecimal(ordersProduct.getUseRed());			// 红包
		ordersProduct.setTotalPrice(totalPrice.subtract(usePoints).subtract(useRed));	// 实际总价-积分-红包
		ordersProduct.setDiscountPrice(totalPrice.subtract(usePoints).subtract(useRed));	// 折扣价
		MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, ordersProduct.getMedicineId());
		ordersProduct.setMedicine(medicine);
		this.iBaseDao.saveObject(ordersProduct);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalOrdersProduct> findOrdersProductList(String ordersId) {
		return this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersId);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delOrdersProduct(MedicalOrdersProduct ordersProduct) {
		this.iBaseDao.deleteObject(ordersProduct);
	}

	@Override
	public boolean validProExist(MedicalOrdersProduct ordersProduct) {
		MedicalOrdersProduct op = (MedicalOrdersProduct) this.iBaseDao.getObjectByHql("from MedicalOrdersProduct a where a.ordersId=? and a.medicine.id=? ", ordersProduct.getOrdersId(), ordersProduct.getMedicineId());
		if(null == op) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void editOrdersStatus(MedicalOrders orders) {
		MedicalOrders bean = (MedicalOrders) this.iBaseDao.getObject(MedicalOrders.class, orders.getId());
		bean.setStatus(SystemConfig.ORDERS_UNDER_TYPE);
		MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, orders.getId());
		ordersStorage.setCallStatus(SystemConfig.ORDERS_UNDER_TYPE);
		ordersStorage.setOrdersStatus(SystemConfig.ORDERS_WAIT_AUDIT_TYPE);
		
		// 计算客户余额、积分、红包
		MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
		customer.setMoney(customer.getMoney().subtract(ordersStorage.getUseMoney()));
		customer.setIntegral(customer.getIntegral() - ordersStorage.getUsePoints());
		customer.setRedEnvelopes(customer.getRedEnvelopes() + ordersStorage.getRed() - ordersStorage.getUseRed());
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page findAllStorageByPage(MedicalOrders orders, Map<String, Object> map) {

		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersStorage a where 1=1 and a.hospitalId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(orders.getHospitalId());
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			countSql.append(" and a.serialNum = ? ");
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			countSql.append(" and a.expressId = ? ");
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			countSql.append(" and a.name = ? ");
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			countSql.append(" and a.checkTime >= ? ");
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getEndTime()) {
			countSql.append(" and a.checkTime <= ? ");
			statusSql.append(" and a.checkTime <= ? ");
			objectList.add(orders.getEndTime());
		}
		
		if(null != orders.getStime()) {
			countSql.append(" and a.createTime >= ? ");
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		if(StringUtils.isNotBlank(orders.getKeyType()) && StringUtils.isNotBlank(orders.getStatus()) ) {
			if(orders.getKeyType().equals(SystemConfig.CM_SYSTEM_TYPE)) {
				countSql.append(" and a.callStatus = ?");
				statusSql.append(" and a.callStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.OM_SYSTEM_TYPE)) {
				countSql.append(" and a.ordersStatus = ?");
				statusSql.append(" and a.ordersStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.WM_SYSTEM_TYPE)) {
				countSql.append(" and a.warehouseStatus = ?");
				statusSql.append(" and a.warehouseStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.FM_SYSTEM_TYPE)) {
				countSql.append(" and a.financialStatus = ?");
				statusSql.append(" and a.financialStatus = ?");
				objectList.add(orders.getStatus());
			}
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}	
				
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersStorage> list = new ArrayList<MedicalOrdersStorage>();
		for(Object obj : page.getResult()) {
			MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) obj;
			// 订单详细
			List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersStorage.getId());
			StringBuilder ordersDetail = new StringBuilder("");
			for(MedicalOrdersProduct ordersProduct : ordersProductList) {
				ordersDetail.append(ordersProduct.getMedicine().getName());
				ordersDetail.append("*");
				ordersDetail.append(ordersProduct.getQuantity());
				ordersDetail.append("；");
			}
			if(StringUtils.isNotBlank(ordersDetail.toString())) {
				ordersDetail.deleteCharAt(ordersDetail.lastIndexOf("；"));
			}
			ordersStorage.setOrdersProduct(ordersDetail.toString());
			
			if(StringUtils.isNotBlank(ordersStorage.getExpressId())) {
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, ordersStorage.getExpressId());
				if(null != logistics) {
					ordersStorage.setExpressName(logistics.getName());
				}
			}
			
			list.add(ordersStorage);
		}
		page.setResult(list);
		return page;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Page findCenterStorageByPage(MedicalOrders orders, Map<String, Object> map, MedicalAdmin admin) {
		
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersStorage a where 1=1 ");
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 ");
		
		List<Object> objectList = new ArrayList<Object>();
		if(!SystemConfig.SUPER_ADMIN.equals(admin.getIsSuper())) {
			countSql.append(" and a.cusSerCenter=? ");
			statusSql.append(" and a.cusSerCenter=? ");
			objectList.add(admin.getGroup().getPlatform().getId());
		}
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			countSql.append(" and a.serialNum = ? ");
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			countSql.append(" and a.expressId = ? ");
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			countSql.append(" and a.name = ? ");
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			countSql.append(" and a.checkTime >= ? ");
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getEndTime()) {
			countSql.append(" and a.checkTime <= ? ");
			statusSql.append(" and a.checkTime <= ? ");
			objectList.add(orders.getEndTime());
		}
		
		if(null != orders.getStime()) {
			countSql.append(" and a.createTime >= ? ");
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		if(StringUtils.isNotBlank(orders.getKeyType()) && StringUtils.isNotBlank(orders.getStatus())) {
			if(orders.getKeyType().equals(SystemConfig.CM_SYSTEM_TYPE)) {
				countSql.append(" and a.callStatus = ?");
				statusSql.append(" and a.callStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.OM_SYSTEM_TYPE)) {
				countSql.append(" and a.ordersStatus = ?");
				statusSql.append(" and a.ordersStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.WM_SYSTEM_TYPE)) {
				countSql.append(" and a.warehouseStatus = ?");
				statusSql.append(" and a.warehouseStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.FM_SYSTEM_TYPE)) {
				countSql.append(" and a.financialStatus = ?");
				statusSql.append(" and a.financialStatus = ?");
				objectList.add(orders.getStatus());
			}
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersStorage> list = new ArrayList<MedicalOrdersStorage>();
		for(Object obj : page.getResult()) {
			MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) obj;
			// 订单详细
			List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersStorage.getId());
			StringBuilder ordersDetail = new StringBuilder("");
			for(MedicalOrdersProduct ordersProduct : ordersProductList) {
				ordersDetail.append(ordersProduct.getMedicine().getName());
				ordersDetail.append("*");
				ordersDetail.append(ordersProduct.getQuantity());
				ordersDetail.append("；");
			}
			if(StringUtils.isNotBlank(ordersDetail.toString())) {
				ordersDetail.deleteCharAt(ordersDetail.lastIndexOf("；"));
			}
			ordersStorage.setOrdersProduct(ordersDetail.toString());
			
			if(StringUtils.isNotBlank(ordersStorage.getExpressId())) {
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, ordersStorage.getExpressId());
				if(null != logistics) {
					ordersStorage.setExpressName(logistics.getName());
				}
			}
			
			list.add(ordersStorage);
		}
		page.setResult(list);
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page findGroupStorageByPage(MedicalOrders orders, Map<String, Object> map, MedicalAdmin admin) {

		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersStorage a where 1=1 ");
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 ");
		
		List<Object> objectList = new ArrayList<Object>();
		if(!SystemConfig.SUPER_ADMIN.equals(admin.getIsSuper())) {
			countSql.append(" and a.cusSerGroup=? ");
			statusSql.append(" and a.cusSerGroup=? ");
			objectList.add(admin.getGroup().getId());
		}
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			countSql.append(" and a.serialNum = ? ");
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			countSql.append(" and a.expressId = ? ");
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			countSql.append(" and a.name = ? ");
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			countSql.append(" and a.checkTime >= ? ");
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getEndTime()) {
			countSql.append(" and a.checkTime <= ? ");
			statusSql.append(" and a.checkTime <= ? ");
			objectList.add(orders.getEndTime());
		}
		
		if(null != orders.getStime()) {
			countSql.append(" and a.createTime >= ? ");
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		if(StringUtils.isNotBlank(orders.getKeyType()) && StringUtils.isNotBlank(orders.getStatus())) {
			if(orders.getKeyType().equals(SystemConfig.CM_SYSTEM_TYPE)) {
				countSql.append(" and a.callStatus = ?");
				statusSql.append(" and a.callStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.OM_SYSTEM_TYPE)) {
				countSql.append(" and a.ordersStatus = ?");
				statusSql.append(" and a.ordersStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.WM_SYSTEM_TYPE)) {
				countSql.append(" and a.warehouseStatus = ?");
				statusSql.append(" and a.warehouseStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.FM_SYSTEM_TYPE)) {
				countSql.append(" and a.financialStatus = ?");
				statusSql.append(" and a.financialStatus = ?");
				objectList.add(orders.getStatus());
			}
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}

		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersStorage> list = new ArrayList<MedicalOrdersStorage>();
		for(Object obj : page.getResult()) {
			MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) obj;
			// 订单详细
			List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersStorage.getId());
			StringBuilder ordersDetail = new StringBuilder("");
			for(MedicalOrdersProduct ordersProduct : ordersProductList) {
				ordersDetail.append(ordersProduct.getMedicine().getName());
				ordersDetail.append("*");
				ordersDetail.append(ordersProduct.getQuantity());
				ordersDetail.append("；");
			}
			if(StringUtils.isNotBlank(ordersDetail.toString())) {
				ordersDetail.deleteCharAt(ordersDetail.lastIndexOf("；"));
			}
			ordersStorage.setOrdersProduct(ordersDetail.toString());
			
			if(StringUtils.isNotBlank(ordersStorage.getExpressId())) {
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, ordersStorage.getExpressId());
				if(null != logistics) {
					ordersStorage.setExpressName(logistics.getName());
				}
			}
			list.add(ordersStorage);
		}
		page.setResult(list);
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Page findMyStorageByPage(MedicalOrders orders, Map<String, Object> map) {

		StringBuilder countSql = new StringBuilder("select count(*) from MedicalOrdersStorage a where 1=1 and a.cusSerId=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 and a.cusSerId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(orders.getCusSerId());
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			countSql.append(" and a.serialNum = ? ");
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			countSql.append(" and a.expressId = ? ");
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			countSql.append(" and a.name = ? ");
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			countSql.append(" and a.checkTime >= ? ");
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getStime()) {
			countSql.append(" and a.createTime >= ? ");
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		if(StringUtils.isNotBlank(orders.getKeyType()) && StringUtils.isNotBlank(orders.getStatus())) {
			if(orders.getKeyType().equals(SystemConfig.CM_SYSTEM_TYPE)) {
				countSql.append(" and a.callStatus = ?");
				statusSql.append(" and a.callStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.OM_SYSTEM_TYPE)) {
				countSql.append(" and a.ordersStatus = ?");
				statusSql.append(" and a.ordersStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.WM_SYSTEM_TYPE)) {
				countSql.append(" and a.warehouseStatus = ?");
				statusSql.append(" and a.warehouseStatus = ?");
				objectList.add(orders.getStatus());
			}
			
			if(orders.getKeyType().equals(SystemConfig.FM_SYSTEM_TYPE)) {
				countSql.append(" and a.financialStatus = ?");
				statusSql.append(" and a.financialStatus = ?");
				objectList.add(orders.getStatus());
			}
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalOrdersStorage> list = new ArrayList<MedicalOrdersStorage>();
		for(Object obj : page.getResult()) {
			MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) obj;
			// 订单详细
			List<MedicalOrdersProduct> ordersProductList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, ordersStorage.getId());
			StringBuilder ordersDetail = new StringBuilder("");
			for(MedicalOrdersProduct ordersProduct : ordersProductList) {
				ordersDetail.append(ordersProduct.getMedicine().getName());
				ordersDetail.append("*");
				ordersDetail.append(ordersProduct.getQuantity());
				ordersDetail.append("；");
			}
			if(StringUtils.isNotBlank(ordersDetail.toString())) {
				ordersDetail.deleteCharAt(ordersDetail.lastIndexOf("；"));
			}
			ordersStorage.setOrdersProduct(ordersDetail.toString());
			
			if(StringUtils.isNotBlank(ordersStorage.getExpressId())) {
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, ordersStorage.getExpressId());
				if(null != logistics) {
					ordersStorage.setExpressName(logistics.getName());
				}
			}
			list.add(ordersStorage);
		}
		page.setResult(list);
		return page;
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void editOrdersStorage(MedicalOrders orders) {
		MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, orders.getId());
		ordersStorage.setName(orders.getName());
		ordersStorage.setContactNo(orders.getContactNo());
		MedicalProvince province = (MedicalProvince) this.iBaseDao.getObject(MedicalProvince.class, orders.getProvinceId());
		MedicalCity city = (MedicalCity) this.iBaseDao.getObject(MedicalCity.class, orders.getCityId());
		MedicalDistrict district = (MedicalDistrict) this.iBaseDao.getObject(MedicalDistrict.class, orders.getDistrictId());
		ordersStorage.setProvince(province);
		ordersStorage.setCity(city);
		ordersStorage.setDistrict(district);
		ordersStorage.setAddress(orders.getAddress());
		ordersStorage.setZipCode(orders.getZipCode());
		ordersStorage.setOrdersRemark(orders.getOrdersRemark());
		ordersStorage.setInvoice(orders.getInvoice());
		ordersStorage.setMediaId(orders.getMediaId());
		ordersStorage.setNature(orders.getNature());
		ordersStorage.setExpressId(orders.getExpressId());
		ordersStorage.setPayType(orders.getPayType());
		ordersStorage.setPayment(orders.getPayment());
		ordersStorage.setFreight(orders.getFreight());
		ordersStorage.setReceivable(orders.getReceivable());
		ordersStorage.setAmountPaid(orders.getAmountPaid());
		ordersStorage.setUsePoints(orders.getUsePoints());
		ordersStorage.setPoints(orders.getPoints());
		ordersStorage.setUseRed(orders.getUseRed());
		ordersStorage.setRed(orders.getRed());
		
		// 待审核、审核通过的订单要实时保存客户余额、积分、红包
		if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(ordersStorage.getOrdersStatus()) || SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(ordersStorage.getOrdersStatus())) {
			MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
			customer.setMoney(orders.getMoney());
			customer.setIntegral(orders.getIntegral());
			customer.setRedEnvelopes(orders.getRedEnvelopes());
		}
		
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void againSubmit(MedicalOrders orders) {
		MedicalOrdersStorage ordersStorage = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, orders.getId());
		if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(ordersStorage.getOrdersStatus())) {
			this.iBaseDao.updateHql("delete MedicalOrdersNoPass a where a.id=? ", ordersStorage.getId());
		} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStorage.getOrdersStatus())) {
			this.iBaseDao.updateHql("delete MedicalOrdersCancel a where a.id=? ", ordersStorage.getId());
		} else {
		}
		ordersStorage.setCallStatus(SystemConfig.ORDERS_UNDER_TYPE);
		ordersStorage.setOrdersStatus(SystemConfig.ORDERS_WAIT_AUDIT_TYPE);
		MedicalOrders bean = new MedicalOrders();
		BeanUtils.copyProperties(ordersStorage, bean);
		bean.setStatus(SystemConfig.ORDERS_UNDER_TYPE);
		bean.setAllocateGrade(SystemConfig.ORDERS_GRADE_INIT);
		this.iBaseDao.addObject(bean);
		
		MedicalCallCustomer customer = (MedicalCallCustomer) this.iBaseDao.getObject(MedicalCallCustomer.class, ordersStorage.getCustomerId());
		customer.setMoney(customer.getMoney().subtract(ordersStorage.getUseMoney()));
		customer.setIntegral(customer.getIntegral() - ordersStorage.getUsePoints());
		customer.setRedEnvelopes(customer.getRedEnvelopes() + ordersStorage.getRed() - ordersStorage.getUseRed());
		
	}
	
	@Override
	public Workbook exportCurrentAll(String ids) throws Exception {
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDER_ALL);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		if(StringUtils.isNotBlank(ids)){
			String [] ordersIds =  ids.split(",");
			for(int j=0;j<ordersIds.length;j++){
				// 获得记录
				MedicalOrdersStorage order = (MedicalOrdersStorage) this.iBaseDao.getObject(MedicalOrdersStorage.class, ordersIds[j]);
				Row currentRow = sheet.createRow(j + 2);
				currentRow.createCell(0).setCellValue(j + 1);
				if(null != order){
					currentRow.createCell(1).setCellValue(order.getSerialNum());
					currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
					currentRow.createCell(3).setCellValue(order.getName());
					
					String callStatus = order.getCallStatus();
					if(SystemConfig.ORDERS_IMPORT_TYPE.equals(callStatus)) {
						callStatus = "正在输入";
					} else if(SystemConfig.ORDERS_UNDER_TYPE.equals(callStatus)) {
						callStatus = "已下单";
					}
					currentRow.createCell(4).setCellValue(callStatus);
					
					String ordersStatus = order.getOrdersStatus();
					if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(ordersStatus)) {
						ordersStatus = "待审核";
					} else if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(ordersStatus)) {
						ordersStatus = "审核通过";
					} else if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(ordersStatus)) {
						ordersStatus = "审核未通过";
					} else if(SystemConfig.ORDERS_AUDIT_INVALID_TYPE.equals(ordersStatus)) {
						ordersStatus = "无效订单";
					} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStatus)) {
						ordersStatus = "取消订单";
					}
					currentRow.createCell(5).setCellValue(ordersStatus);
					
					String warehouseStatus = order.getWarehouseStatus();
					if(SystemConfig.ORDERS_WAIT_DELIVERY_TYPE.equals(warehouseStatus)) {
						warehouseStatus =  "待发货";
					} else if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(warehouseStatus)) {
						warehouseStatus =  "已发货";
					} else if(SystemConfig.ORDERS_RETREAT_TYPE.equals(warehouseStatus)) {
						warehouseStatus =  "退单";
					} else if(SystemConfig.ORDERS_SIGN_TYPE.equals(warehouseStatus)) {
						warehouseStatus =  "签收";
					}
					currentRow.createCell(6).setCellValue(warehouseStatus);
					
					String financialStatus = order.getFinancialStatus();
					if(SystemConfig.BE_RECONCILED.equals(financialStatus)) {
						financialStatus = "待对账";
					} else if(SystemConfig.HAS_RECONCILED.equals(financialStatus)) {
						financialStatus = "已对账";
					} else if(SystemConfig.HAS_SECTIONED.equals(financialStatus)) {
						financialStatus = "已回款";
					}
					currentRow.createCell(7).setCellValue(financialStatus);
					currentRow.createCell(8).setCellValue(order.getContactNo());
					BigDecimal total = new BigDecimal(0);
					if(null != order.getPayment()) {
						total = total.add(order.getPayment());
					}
					if(null != order.getFreight()) {
						total = total.add(order.getFreight());
					}
					currentRow.createCell(9).setCellValue(total.toString());
					currentRow.createCell(10).setCellValue(order.getProvince().getpName());
					currentRow.createCell(11).setCellValue(order.getCity().getcName());
					currentRow.createCell(12).setCellValue(order.getDistrict().getdName());
					currentRow.createCell(13).setCellValue(order.getAddress());
					
					MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
					if(null != logistics) {
						currentRow.createCell(14).setCellValue(logistics.getName());
					}
					currentRow.createCell(15).setCellValue(order.getExpressNo());
					if(null != order.getDeliveryTime()) {
						currentRow.createCell(16).setCellValue(DateFormatUtils.format(order.getDeliveryTime(), "yyyy-MM-dd HH:mm:ss"));
					}
				}
			}
		}
		return workbook;
	}
	
	@Override
	public Workbook allExportCurrentAll(MedicalOrders orders) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		objectList.add(user.getEnterpriseId());
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getEndTime()) {
			statusSql.append(" and a.checkTime <= ? ");
			objectList.add(orders.getEndTime());
		}
		
		if(null != orders.getStime()) {
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		statusSql.append("order by a.createTime desc ");
		
		@SuppressWarnings("unchecked")
		List<MedicalOrdersStorage> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDER_ALL);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersStorage order = list.get(j);
			Row currentRow = sheet.createRow(j + 2);
			currentRow.createCell(0).setCellValue(j + 1);
			if(null != order){
				currentRow.createCell(1).setCellValue(order.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(order.getName());
				
				String callStatus = order.getCallStatus();
				if(SystemConfig.ORDERS_IMPORT_TYPE.equals(callStatus)) {
					callStatus = "正在输入";
				} else if(SystemConfig.ORDERS_UNDER_TYPE.equals(callStatus)) {
					callStatus = "已下单";
				}
				currentRow.createCell(4).setCellValue(callStatus);
				
				String ordersStatus = order.getOrdersStatus();
				if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(ordersStatus)) {
					ordersStatus = "待审核";
				} else if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核通过";
				} else if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核未通过";
				} else if(SystemConfig.ORDERS_AUDIT_INVALID_TYPE.equals(ordersStatus)) {
					ordersStatus = "无效订单";
				} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStatus)) {
					ordersStatus = "取消订单";
				}
				currentRow.createCell(5).setCellValue(ordersStatus);
				
				String warehouseStatus = order.getWarehouseStatus();
				if(SystemConfig.ORDERS_WAIT_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "待发货";
				} else if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "已发货";
				} else if(SystemConfig.ORDERS_RETREAT_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "退单";
				} else if(SystemConfig.ORDERS_SIGN_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "签收";
				}
				currentRow.createCell(6).setCellValue(warehouseStatus);
				
				String financialStatus = order.getFinancialStatus();
				if(SystemConfig.BE_RECONCILED.equals(financialStatus)) {
					financialStatus = "待对账";
				} else if(SystemConfig.HAS_RECONCILED.equals(financialStatus)) {
					financialStatus = "已对账";
				} else if(SystemConfig.HAS_SECTIONED.equals(financialStatus)) {
					financialStatus = "已回款";
				}
				currentRow.createCell(7).setCellValue(financialStatus);
				currentRow.createCell(8).setCellValue(order.getContactNo());
				BigDecimal total = new BigDecimal(0);
				if(null != order.getPayment()) {
					total = total.add(order.getPayment());
				}
				if(null != order.getFreight()) {
					total = total.add(order.getFreight());
				}
				currentRow.createCell(9).setCellValue(total.toString());
				currentRow.createCell(10).setCellValue(order.getProvince().getpName());
				currentRow.createCell(11).setCellValue(order.getCity().getcName());
				currentRow.createCell(12).setCellValue(order.getDistrict().getdName());
				currentRow.createCell(13).setCellValue(order.getAddress());
				
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
				if(null != logistics) {
					currentRow.createCell(14).setCellValue(logistics.getName());
				}
				currentRow.createCell(15).setCellValue(order.getExpressNo());
				if(null != order.getDeliveryTime()) {
					currentRow.createCell(16).setCellValue(DateFormatUtils.format(order.getDeliveryTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}
	
	@Override
	public Workbook allExportCenter(MedicalOrders orders, MedicalAdmin admin) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 ");
		
		List<Object> objectList = new ArrayList<Object>();
		if(!SystemConfig.SUPER_ADMIN.equals(admin.getIsSuper())) {
			statusSql.append(" and a.cusSerCenter=? ");
			objectList.add(admin.getGroup().getPlatform().getId());
		}
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getEndTime()) {
			statusSql.append(" and a.checkTime <= ? ");
			objectList.add(orders.getEndTime());
		}
		
		if(null != orders.getStime()) {
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		statusSql.append("order by a.createTime desc ");
		
		@SuppressWarnings("unchecked")
		List<MedicalOrdersStorage> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDER_ALL);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersStorage order = list.get(j);
			Row currentRow = sheet.createRow(j + 2);
			currentRow.createCell(0).setCellValue(j + 1);
			if(null != order){
				currentRow.createCell(1).setCellValue(order.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(order.getName());
				
				String callStatus = order.getCallStatus();
				if(SystemConfig.ORDERS_IMPORT_TYPE.equals(callStatus)) {
					callStatus = "正在输入";
				} else if(SystemConfig.ORDERS_UNDER_TYPE.equals(callStatus)) {
					callStatus = "已下单";
				}
				currentRow.createCell(4).setCellValue(callStatus);
				
				String ordersStatus = order.getOrdersStatus();
				if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(ordersStatus)) {
					ordersStatus = "待审核";
				} else if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核通过";
				} else if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核未通过";
				} else if(SystemConfig.ORDERS_AUDIT_INVALID_TYPE.equals(ordersStatus)) {
					ordersStatus = "无效订单";
				} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStatus)) {
					ordersStatus = "取消订单";
				}
				currentRow.createCell(5).setCellValue(ordersStatus);
				
				String warehouseStatus = order.getWarehouseStatus();
				if(SystemConfig.ORDERS_WAIT_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "待发货";
				} else if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "已发货";
				} else if(SystemConfig.ORDERS_RETREAT_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "退单";
				} else if(SystemConfig.ORDERS_SIGN_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "签收";
				}
				currentRow.createCell(6).setCellValue(warehouseStatus);
				
				String financialStatus = order.getFinancialStatus();
				if(SystemConfig.BE_RECONCILED.equals(financialStatus)) {
					financialStatus = "待对账";
				} else if(SystemConfig.HAS_RECONCILED.equals(financialStatus)) {
					financialStatus = "已对账";
				} else if(SystemConfig.HAS_SECTIONED.equals(financialStatus)) {
					financialStatus = "已回款";
				}
				currentRow.createCell(7).setCellValue(financialStatus);
				currentRow.createCell(8).setCellValue(order.getContactNo());
				BigDecimal total = new BigDecimal(0);
				if(null != order.getPayment()) {
					total = total.add(order.getPayment());
				}
				if(null != order.getFreight()) {
					total = total.add(order.getFreight());
				}
				currentRow.createCell(9).setCellValue(total.toString());
				currentRow.createCell(10).setCellValue(order.getProvince().getpName());
				currentRow.createCell(11).setCellValue(order.getCity().getcName());
				currentRow.createCell(12).setCellValue(order.getDistrict().getdName());
				currentRow.createCell(13).setCellValue(order.getAddress());
				
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
				if(null != logistics) {
					currentRow.createCell(14).setCellValue(logistics.getName());
				}
				currentRow.createCell(15).setCellValue(order.getExpressNo());
				if(null != order.getDeliveryTime()) {
					currentRow.createCell(16).setCellValue(DateFormatUtils.format(order.getDeliveryTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}
	
	@Override
	public Workbook allExportGroup(MedicalOrders orders, MedicalAdmin admin) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where 1=1 ");
		
		List<Object> objectList = new ArrayList<Object>();
		if(!SystemConfig.SUPER_ADMIN.equals(admin.getIsSuper())) {
			statusSql.append(" and a.cusSerGroup=? ");
			objectList.add(admin.getGroup().getId());
		}
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getEndTime()) {
			statusSql.append(" and a.checkTime <= ? ");
			objectList.add(orders.getEndTime());
		}
		
		if(null != orders.getStime()) {
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		statusSql.append("order by a.createTime desc ");
		
		@SuppressWarnings("unchecked")
		List<MedicalOrdersStorage> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDER_ALL);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersStorage order = list.get(j);
			Row currentRow = sheet.createRow(j + 2);
			currentRow.createCell(0).setCellValue(j + 1);
			if(null != order){
				currentRow.createCell(1).setCellValue(order.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(order.getName());
				
				String callStatus = order.getCallStatus();
				if(SystemConfig.ORDERS_IMPORT_TYPE.equals(callStatus)) {
					callStatus = "正在输入";
				} else if(SystemConfig.ORDERS_UNDER_TYPE.equals(callStatus)) {
					callStatus = "已下单";
				}
				currentRow.createCell(4).setCellValue(callStatus);
				
				String ordersStatus = order.getOrdersStatus();
				if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(ordersStatus)) {
					ordersStatus = "待审核";
				} else if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核通过";
				} else if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核未通过";
				} else if(SystemConfig.ORDERS_AUDIT_INVALID_TYPE.equals(ordersStatus)) {
					ordersStatus = "无效订单";
				} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStatus)) {
					ordersStatus = "取消订单";
				}
				currentRow.createCell(5).setCellValue(ordersStatus);
				
				String warehouseStatus = order.getWarehouseStatus();
				if(SystemConfig.ORDERS_WAIT_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "待发货";
				} else if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "已发货";
				} else if(SystemConfig.ORDERS_RETREAT_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "退单";
				} else if(SystemConfig.ORDERS_SIGN_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "签收";
				}
				currentRow.createCell(6).setCellValue(warehouseStatus);
				
				String financialStatus = order.getFinancialStatus();
				if(SystemConfig.BE_RECONCILED.equals(financialStatus)) {
					financialStatus = "待对账";
				} else if(SystemConfig.HAS_RECONCILED.equals(financialStatus)) {
					financialStatus = "已对账";
				} else if(SystemConfig.HAS_SECTIONED.equals(financialStatus)) {
					financialStatus = "已回款";
				}
				currentRow.createCell(7).setCellValue(financialStatus);
				currentRow.createCell(8).setCellValue(order.getContactNo());
				BigDecimal total = new BigDecimal(0);
				if(null != order.getPayment()) {
					total = total.add(order.getPayment());
				}
				if(null != order.getFreight()) {
					total = total.add(order.getFreight());
				}
				currentRow.createCell(9).setCellValue(total.toString());
				currentRow.createCell(10).setCellValue(order.getProvince().getpName());
				currentRow.createCell(11).setCellValue(order.getCity().getcName());
				currentRow.createCell(12).setCellValue(order.getDistrict().getdName());
				currentRow.createCell(13).setCellValue(order.getAddress());
				
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
				if(null != logistics) {
					currentRow.createCell(14).setCellValue(logistics.getName());
				}
				currentRow.createCell(15).setCellValue(order.getExpressNo());
				if(null != order.getDeliveryTime()) {
					currentRow.createCell(16).setCellValue(DateFormatUtils.format(order.getDeliveryTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}
	
	@Override
	public Workbook allExportMy(MedicalOrders orders) throws Exception {
		StringBuilder statusSql = new StringBuilder("from MedicalOrdersStorage a where a.cusSerId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(orders.getCusSerId());
		
		if(StringUtils.isNotBlank(orders.getSerialNum())) {
			statusSql.append(" and a.serialNum = ? ");
			objectList.add(orders.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(orders.getExpressId())) {
			statusSql.append(" and a.expressId = ? ");
			objectList.add(orders.getExpressId());
		}
		
		if(StringUtils.isNotBlank(orders.getName())) {
			statusSql.append(" and a.name = ? ");
			objectList.add(orders.getName());
		}
		
		if(null != orders.getStartTime()) {
			statusSql.append(" and a.checkTime >= ? ");
			objectList.add(orders.getStartTime());
		}
		
		if(null != orders.getEndTime()) {
			statusSql.append(" and a.checkTime <= ? ");
			objectList.add(orders.getEndTime());
		}
		
		if(null != orders.getStime()) {
			statusSql.append(" and a.createTime >= ? ");
			objectList.add(orders.getStime());
		}
		
		statusSql.append("order by a.createTime desc ");
		
		@SuppressWarnings("unchecked")
		List<MedicalOrdersStorage> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.ORDER_ALL);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		int size = list.size();
		for(int j = 0;j < size;j++){
			// 获得记录
			MedicalOrdersStorage order = list.get(j);
			Row currentRow = sheet.createRow(j + 2);
			currentRow.createCell(0).setCellValue(j + 1);
			if(null != order){
				currentRow.createCell(1).setCellValue(order.getSerialNum());
				currentRow.createCell(2).setCellValue(DateFormatUtils.format(order.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));
				currentRow.createCell(3).setCellValue(order.getName());
				
				String callStatus = order.getCallStatus();
				if(SystemConfig.ORDERS_IMPORT_TYPE.equals(callStatus)) {
					callStatus = "正在输入";
				} else if(SystemConfig.ORDERS_UNDER_TYPE.equals(callStatus)) {
					callStatus = "已下单";
				}
				currentRow.createCell(4).setCellValue(callStatus);
				
				String ordersStatus = order.getOrdersStatus();
				if(SystemConfig.ORDERS_WAIT_AUDIT_TYPE.equals(ordersStatus)) {
					ordersStatus = "待审核";
				} else if(SystemConfig.ORDERS_AUDIT_PASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核通过";
				} else if(SystemConfig.ORDERS_AUDIT_NOPASS_TYPE.equals(ordersStatus)) {
					ordersStatus = "审核未通过";
				} else if(SystemConfig.ORDERS_AUDIT_INVALID_TYPE.equals(ordersStatus)) {
					ordersStatus = "无效订单";
				} else if(SystemConfig.ORDERS_AUDIT_CANCEL_TYPE.equals(ordersStatus)) {
					ordersStatus = "取消订单";
				}
				currentRow.createCell(5).setCellValue(ordersStatus);
				
				String warehouseStatus = order.getWarehouseStatus();
				if(SystemConfig.ORDERS_WAIT_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "待发货";
				} else if(SystemConfig.ORDERS_DELIVERY_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "已发货";
				} else if(SystemConfig.ORDERS_RETREAT_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "退单";
				} else if(SystemConfig.ORDERS_SIGN_TYPE.equals(warehouseStatus)) {
					warehouseStatus =  "签收";
				}
				currentRow.createCell(6).setCellValue(warehouseStatus);
				
				String financialStatus = order.getFinancialStatus();
				if(SystemConfig.BE_RECONCILED.equals(financialStatus)) {
					financialStatus = "待对账";
				} else if(SystemConfig.HAS_RECONCILED.equals(financialStatus)) {
					financialStatus = "已对账";
				} else if(SystemConfig.HAS_SECTIONED.equals(financialStatus)) {
					financialStatus = "已回款";
				}
				currentRow.createCell(7).setCellValue(financialStatus);
				currentRow.createCell(8).setCellValue(order.getContactNo());
				BigDecimal total = new BigDecimal(0);
				if(null != order.getPayment()) {
					total = total.add(order.getPayment());
				}
				if(null != order.getFreight()) {
					total = total.add(order.getFreight());
				}
				currentRow.createCell(9).setCellValue(total.toString());
				currentRow.createCell(10).setCellValue(order.getProvince().getpName());
				currentRow.createCell(11).setCellValue(order.getCity().getcName());
				currentRow.createCell(12).setCellValue(order.getDistrict().getdName());
				currentRow.createCell(13).setCellValue(order.getAddress());
				
				MedicalLogistics logistics = (MedicalLogistics) this.iBaseDao.getObject(MedicalLogistics.class, order.getExpressId());
				if(null != logistics) {
					currentRow.createCell(14).setCellValue(logistics.getName());
				}
				currentRow.createCell(15).setCellValue(order.getExpressNo());
				if(null != order.getDeliveryTime()) {
					currentRow.createCell(16).setCellValue(DateFormatUtils.format(order.getDeliveryTime(), "yyyy-MM-dd HH:mm:ss"));
				}
			}
		}
		return workbook;
	}

	@Override
	public MedicalOrdersStorage findByOId(String ordersId) {
		return (MedicalOrdersStorage) this.iBaseDao.getObjectByHql("from MedicalOrdersStorage m where m.id = ? ", ordersId);
	}

	@Override
	public Long getOrdersCount(String customerId, String ordersId) {
		Long ordersCount = (long) this.iBaseDao.getIntCount("select count(*) from MedicalOrdersStorage a where a.customerId=? and a.callStatus=? and a.id != ? ", customerId, SystemConfig.ORDERS_UNDER_TYPE, ordersId);
		return ordersCount;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalOrdersStorage> findHistoryList(String customerId,
			String ordersId) {
		List<MedicalOrdersStorage> list = this.iBaseDao.getBySql("from MedicalOrdersStorage a where a.customerId=? and a.callStatus=? and a.id != ? ", null, customerId, SystemConfig.ORDERS_UNDER_TYPE, ordersId);
		List<MedicalOrdersStorage> beanList = new ArrayList<MedicalOrdersStorage>();
		for(MedicalOrdersStorage bean : list) {
			String ordersProduct = "";
			MedicalKeyValue payType = (MedicalKeyValue)this.iBaseDao.getOmValueByKey(bean.getHospitalId(), bean.getPayType());
			bean.setPayTypeVal(payType.getKeyValue());
			List<MedicalOrdersProduct> opList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, bean.getId());
			for(MedicalOrdersProduct op : opList) {
				ordersProduct += op.getMedicine().getName() + ";"; 
			}
			bean.setOrdersProduct(ordersProduct);
			beanList.add(bean);
		}
		return beanList;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void calcDiscount(MedicalOrders orders) {
		// 计算折扣
		BigDecimal discount = orders.getReceivable().divide(orders.getOriReceivable(), 5, RoundingMode.HALF_UP);
		List<MedicalOrdersProduct> productList = this.iBaseDao.getBySql("from MedicalOrdersProduct a where a.ordersId=? ", null, orders.getId());
		for(MedicalOrdersProduct product : productList) {
			this.iBaseDao.updateHql("update MedicalOrdersProduct a set a.discountPrice=? where a.id=? ", product.getDiscountPrice().multiply(discount), product.getId());
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalOrders> findInputOrders(String customerId) {
		
		List<MedicalOrders> list = this.iBaseDao.getBySql("from MedicalOrders a where a.customerId=? and a.status=? ", null, customerId, SystemConfig.ORDERS_IMPORT_TYPE);
		
//		for(MedicalOrders orders : list) {
//			
//			// 积分
//			
//			// 红包
//			
//			// 余额
//			
//			
//		}
		
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalKeyValue> findSystemList(String typeName) {
		List<MedicalKeyValue> keyValList = this.iBaseDao.getBySql("from MedicalKeyValue a where a.typeName=? ", null, typeName);
		Iterator<MedicalKeyValue> keyValIter = keyValList.iterator();
		while(keyValIter.hasNext()) {
			MedicalKeyValue keyVal = keyValIter.next();
			if(SystemConfig.HM_SYSTEM_TYPE.equals(keyVal.getKeyName()) || 
					SystemConfig.SS_SYSTEM_TYPE.equals(keyVal.getKeyName()) || 
					SystemConfig.MM_SYSTEM_TYPE.equals(keyVal.getKeyName())) {
				keyValIter.remove();
			}
		}
		return keyValList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalKeyValue> findOrderStatusList(String systemType) {
		List<MedicalKeyValue> keyValList = this.iBaseDao.getBySql("from MedicalKeyValue a where a.typeName=? and a.systemType=? ", null, SystemConfig.ORDERS_STATUS, systemType);
		return keyValList;
	}
}
